﻿IF OBJECT_ID('up_KML_InspVisitDistributionPoles') IS not NULL
	DROP PROC up_KML_InspVisitDistributionPoles 

GO  

--[up_KML_InspVisitDistributionPole] 20

CREATE PROCEDURE [dbo].[up_KML_InspVisitDistributionPoles]
	 @id int 
AS


DECLARE @DistributionInspectionVisitsList varchar(MAX)
SET @DistributionInspectionVisitsList = ''

SELECT 
	 @DistributionInspectionVisitsList = @DistributionInspectionVisitsList + CONVERT(varchar, [Vendor_Distribution_Poles_PoleId]) + ','
FROM v_tb_Vendor_Distribution_Poles Pole
WHERE 
	Pole.[ProjectFk] = @id


declare @settingValue varchar(500)
set @settingValue = (select SettingValue from tb_Settings where SettingId = 26)

declare @template varchar(max);
set @template = '
	<iframe 
		src="{url}{page}?ID=$[ID]&HideBackLink=1" 
		style="width: 325px; height: 400px;"
		frameborder="0"  marginwidth="0" marginheight="0">Pole Position</iframe>
		$[description]'
select @template = replace(@template, '{url}', SettingValue)
from tb_Settings where SettingId = 23

declare @xml varchar(max);
select @xml = replace(convert(varchar(max),
(select 
	 'Pole Position Desktop' as 'Document/name'
	-- Is the mapicon filename correct?
	,(select
		'InspectionPoleDistribution' as '@id'
		,.75 as 'IconStyle/scale'
		,replace(@template, '{page}', 'InspVisitDistributionPole.aspx') as 'BalloonStyle/text'
		,Replace(@settingValue, '{0}', 'mapicon_visit.png') as 'IconStyle/Icon/href'
		for xml path('Style'), type) as 'Document'

		,(select 
			-- what test shold show up on the map?
			List.Value as 'name'

			-- all assets of this type get the same base style
			,'#InspectionPoleDistribution' as 'styleUrl'

			-- should we show a special icon?
			--,@DistributionInspectionVisitsIcon as 'Style/IconStyle/Icon/href'

			--,case
			--	when @DistributionInspectionVisitsIcon != null then @DistributionInspectionVisitsIcon
			--	else
			,		case Poles.ElectricalProblem
						when 1 then Replace(@settingValue, '{0}', 'mapicon_visit_electricproblem.png')
						else
						case Poles.InspectionResult
							when '1' then Replace(@settingValue, '{0}', 'mapicon_visit_okay.png')
							when '2' then Replace(@settingValue, '{0}', 'mapicon_visit_warning.png')
							when '3-1' then Replace(@settingValue, '{0}', 'mapicon_visit_redflag.png')
							when '3-2' then Replace(@settingValue, '{0}', 'mapicon_visit_redflag.png')
							when '3-3' then Replace(@settingValue, '{0}', 'mapicon_visit_redflag.png')
							when '3-4' then Replace(@settingValue, '{0}', 'mapicon_visit_redflag.png')
							else Replace(@settingValue, '{0}', 'mapicon_visit.png')
						end
					end as 'Style/IconStyle/Icon/href'
			 --end as 'Style/IconStyle/Icon/href'

			-- should we show it in a special color?
			--,@DistributionInspectionVisitsColor as 'Style/IconStyle/color'

			,'ID' as 'ExtendedData/Data/@name'
			,List.Value as 'ExtendedData/Data/value'

			-- where is it?
			,Convert(nvarchar, Longitude, 128) + ',' + Convert(nvarchar, Latitude, 128) + ',0' as 'Point/coordinates'
		from
			(select * from dbo.fn_Split(@DistributionInspectionVisitsList, default)) as List
			join 
				(
				select 
					tb_Vendor_Distribution_Poles.*
					,IsNull(results.InspectionResult, 'None') as InspectionResult
					,isNull(results.ElectricalProblem, 0) as ElectricalProblem
					,CASE
						WHEN us.SettingValue = '0' THEN TopType --New Top type style display
						ELSE ISNULL(OldStyleTopType, 'N/A') --Old Type style display
					END TopType
					--,isNull(Tops.TopType, 'None') as TopType
				from 
					tb_Vendor_Distribution_Poles
					
					-- top type
					left join (
						select 
							 Max(Tops.Name) as TopType
							,Max(Tops.OldStyleName) as OldStyleTopType
							,poleTops.Vendor_Distribution_PoleFk
						from 
							tb_Vendor_Distribution_TopsXPole poleTops
							join tb_Distributions_TopTypes Tops
								on Tops.Distributions_TopTypeId = poleTops.Vendor_Distribution_TopFk				
						--where
						--	@DistributionInspectionVisitsText = 'Top'
						group by 
							poleTops.Vendor_Distribution_PoleFk
					) as tops on tb_Vendor_Distribution_Poles.Vendor_Distribution_Poles_PoleId = tops.Vendor_Distribution_PoleFk
					
					-- inspection result + elec prob
					left join (
						select 
						 Max(resultTypes.Name) as InspectionResult
						,Max(convert(int, isnull(inspectionResults.bElectrical, 0))) as ElectricalProblem
						,inspectionResults.Vendor_Distribution_PoleFk
						from 
							tb_Vendor_Distribution_InspectionResults as inspectionResults 
							join tb_vendor_inspvisit_xmissionstructures_resultTypes resultTypes 
								on inspectionResults.Vendor_Distribution_ResultTypeFk = resultTypes.Vendor_InspVisit_XmissionStructures_ResultTypeId				
						group by 
							inspectionResults.Vendor_Distribution_PoleFk
					) as results 
							on tb_Vendor_Distribution_Poles.Vendor_Distribution_Poles_PoleId = results.Vendor_Distribution_PoleFk
					INNER JOIN tb_Vendor_Projects projects
						ON projects.Vendor_ProjectId = tb_Vendor_Distribution_Poles.ProjectFk
					INNER JOIN tb_Utility_Settings us
						ON us.UtilityFk = projects.UtilityFk
						AND us.SettingName = 'TopTypeDisplayStyle'
				) as Poles
					on Poles.Vendor_Distribution_Poles_PoleId = Convert(int, List.Value)

			-- include setting for custom icon
			-- cross join tb_Settings 
			where 
				Poles.DistributionType = 1 --Inspection Type
				-- and SettingId = 26
		for xml path('Placemark'), TYPE)  as 'Document'	

for xml path('kml'))), '<kml>', '<?xml version="1.0" encoding="UTF-8"?><kml xmlns="http://earth.google.com/kml/2.2">')

select @xml;



